/**
 * 
 */
package com.wolfpire.proms.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.proms.dao.ProjectIndexDao;
import com.wolfpire.proms.dao.TaskDao;
import com.wolfpire.proms.model.Task;
import com.wolfpire.system.common.Constants;
import com.wolfpire.system.common.EasyuiPage;
import com.wolfpire.system.common.Page;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.workflow.em.ActorType;

/**
 * @author lihd
 *
 */

@Repository("taskDao")
public class TaskDaoImpl extends BaseHibernateDao<Task, Long> implements
		TaskDao {
	
	@Resource ProjectIndexDao projectIndexDao;

	@Override
	public Page<Task> findTasks(Page<Task> page, Task filterTask) {
		Criterion[] criterions = this.createFilter(filterTask, true);
		int totalCount = this.findIntByCriteria(criterions);
		page.setTotalCount(totalCount);
		
		if (0 < totalCount) {
			Criteria c = this.createCriteria(criterions);
			c.addOrder(Order.desc("createTime"));
			c = this.setPageParameter(c, page);
			@SuppressWarnings("unchecked")
			List<Task> result = c.list();
			page.setDataList(result);
		}
		return page;
	}
	
	private Criterion[] createFilter(Task filterTask, boolean cascadeProjectIndexId) {
		if (null == filterTask) {
			return null;
		}
		List<Criterion> criterions = new ArrayList<Criterion>();
		if (null != filterTask.getId()) {
			criterions.add(Restrictions.eq("id", filterTask.getId()));
		}
		if (!StringUtils.isBlank(filterTask.getName())) {
			criterions.add(Restrictions.like("name", filterTask.getName(), MatchMode.ANYWHERE));
		}
		if (!StringUtils.isBlank(filterTask.getRemark())) {
			criterions.add(Restrictions.like("remark", filterTask.getRemark(), MatchMode.ANYWHERE));
		}
		if (null != filterTask.getProjectIndexId()) {
			if (cascadeProjectIndexId) {
				List<Long> projectIndexIds = this.projectIndexDao.findAllIds(filterTask.getProjectIndexId()); 
						//this.getAllTaskIdsByProjectIndexId(filterTask.getProjectIndexId());
				criterions.add(Restrictions.in("projectIndexId", projectIndexIds));
			} else {
				criterions.add(Restrictions.eq("projectIndexId", filterTask.getProjectIndexId()));
			}
		}
		if (null != filterTask.getStatus()) {
			criterions.add(Restrictions.eq("status", filterTask.getStatus()));
		}
		if (null != filterTask.getDelFlag()) {
			criterions.add(Restrictions.eq("delFlag", filterTask.getDelFlag()));
		}
		// TOOD : 有坑，后面再调整
		if (!StringUtils.isBlank(filterTask.getProgressStatus())) {
			if (Constants.PROGRESS_LEVEL_1.equals(filterTask.getProgressStatus())) {
				criterions.add(Restrictions.or(Restrictions.between("progress", 0.00D, 25.00D), Restrictions.isNull("progress")));
			} else if (Constants.PROGRESS_LEVEL_2.equals(filterTask.getProgressStatus())) {
				criterions.add(Restrictions.between("progress", 25.01D, 50.0D));
			} else if (Constants.PROGRESS_LEVEL_3.equals(filterTask.getProgressStatus())) {
				criterions.add(Restrictions.between("progress", 50.01D, 75.00D));
			} else if (Constants.PROGRESS_LEVEL_4.equals(filterTask.getProgressStatus())) {
				criterions.add(Restrictions.between("progress", 75.01D, 99.99D));
			} else if (Constants.PROGRESS_LEVEL_5.equals(filterTask.getProgressStatus())) {
				criterions.add(Restrictions.eq("progress", 100.00D));
			}
		}
		return criterions.toArray(new Criterion[criterions.size()]);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Page<Map<String, Object>> findApplyTasks(Page<Map<String, Object>> page, Task filterTask, String formTypeId) {
		
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySqlBuffer.append(" SELECT t.id, t.name, t.remark, t.plan_amount, t.fact_amount, ")
						.append("  DATE_FORMAT( t.start_time, '%Y-%m-%d %H:%i:%s' ) as start_time, ")
						.append("  DATE_FORMAT( t.end_time, '%Y-%m-%d %H:%i:%s' ) as end_time, ")
						.append(" t.project_index_id, t.progress, t.task_actor_names,  ")
						.append(" DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, t.create_user_id, t.create_user_name, ")
						.append(" DATE_FORMAT( t.update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, t.update_user_id, t.update_user_name, ")
						.append(" t.status, t.del_flag, i.instance_id, i.STATUS as wf_status, i.cur_node ")
						.append(" FROM t_task t ")
						.append(" LEFT JOIN wf_process_instance i ON t.id = i.FORM_ID ")
						.append(" AND i.FORM_TYPE_ID =:formTypeId ")
						.append(" WHERE ")
						.append(" t.del_flag =:delFlag ")
						.append(" AND t.create_user_id =:createUserId ");
		propertyNames.add("formTypeId");
		propertyValues.add(formTypeId);
		propertyNames.add("delFlag");
		propertyValues.add(Constants.NORMAL_FLAG);
		propertyNames.add("createUserId");
		propertyValues.add(filterTask.getCreateUserId());
		
		this.createQuerySql(filterTask, querySqlBuffer, propertyNames, propertyValues);
		
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		
		/*if (null != filterTask.getCreateTime()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) >= DATE_FORMAT(:createTime, '%Y-%m-%d %H:%i:%s') ");
			propertyNames.add("createTime");
			propertyValues.add(filterTask.getCreateTime());
		}
		if (null != filterTask.getId()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) =< DATE_FORMAT(?, '%Y-%m-%d %H:%i:%s') ");
		}*/
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY t.status ASC,t.create_time DESC");
		if (0 < totalCount) {
			List<Map<String, Object>> list = this.setPageParameter(page, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(list);
		}
		return page;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	private void createQuerySql(Task filterTask, StringBuffer querySqlBuffer, List<String> propertyNames, List propertyValues) {
		if (null != filterTask.getId()) {
			querySqlBuffer.append(" AND t.id =:id ");
			propertyNames.add("id");
			propertyValues.add(filterTask.getId());
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterTask.getName()))) {
			querySqlBuffer.append(" AND t.name LIKE :name ");
			propertyNames.add("name");
			propertyValues.add("%" + filterTask.getName() + "%");
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterTask.getRemark()))) {
			querySqlBuffer.append(" AND t.remark LIKE :remark ");
			propertyNames.add("remark");
			propertyValues.add("%" + filterTask.getRemark() + "%");
		}
		if (!StringUtils.isBlank(filterTask.getProgressStatus())) {
			if (Constants.PROGRESS_LEVEL_1.equals(filterTask.getProgressStatus())) {
				querySqlBuffer.append(" AND IFNULL(t.progress, 0.00) BETWEEN :progress1 AND :progress2 ");
				propertyNames.add("progress1");
				propertyValues.add(0.00D);
				propertyNames.add("progress2");
				propertyValues.add(25.00D);
			} else if (Constants.PROGRESS_LEVEL_2.equals(filterTask.getProgressStatus())) {
				querySqlBuffer.append(" AND IFNULL(t.progress, 0.00) BETWEEN :progress1 AND :progress2 ");
				propertyNames.add("progress1");
				propertyValues.add(25.01D);
				propertyNames.add("progress2");
				propertyValues.add(50.00D);
			} else if (Constants.PROGRESS_LEVEL_3.equals(filterTask.getProgressStatus())) {
				querySqlBuffer.append(" AND IFNULL(t.progress, 0.00) BETWEEN :progress1 AND :progress2 ");
				propertyNames.add("progress1");
				propertyValues.add(50.01D);
				propertyNames.add("progress2");
				propertyValues.add(75.00D);
			} else if (Constants.PROGRESS_LEVEL_4.equals(filterTask.getProgressStatus())) {
				querySqlBuffer.append(" AND IFNULL(t.progress, 0.00) BETWEEN :progress1 AND :progress2 ");
				propertyNames.add("progress1");
				propertyValues.add(75.01D);
				propertyNames.add("progress2");
				propertyValues.add(99.99D);
			} else if (Constants.PROGRESS_LEVEL_5.equals(filterTask.getProgressStatus())) {
				querySqlBuffer.append(" AND IFNULL(t.progress, 0.00) = :progress ");
				propertyNames.add("progress");
				propertyValues.add(100.00D);
			}
		}
		if (null != filterTask.getProjectIndexId()) {
			querySqlBuffer.append(" AND t.project_index_id in (SELECT id FROM t_project_index WHERE FIND_IN_SET(id, queryChildrenProjectIndex(:projectIndexId))) ");
			propertyNames.add("projectIndexId");
			propertyValues.add(filterTask.getProjectIndexId());
		}
		if (null != filterTask.getStatus()) {
			querySqlBuffer.append(" and t.status = :status");
			propertyNames.add("status");
			propertyValues.add(filterTask.getStatus());
		}
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Page<Map<String, Object>> findTodoTasks(
			EasyuiPage<Map<String, Object>> page, Task filterTask,
			String formTypeId) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySqlBuffer.append(" SELECT t.id, t.name, t.remark, t.plan_amount, t.fact_amount, t.project_index_id, t.progress, t.task_actor_names, ")
						.append("  DATE_FORMAT( t.start_time, '%Y-%m-%d %H:%i:%s' ) as start_time, ")
						.append("  DATE_FORMAT( t.end_time, '%Y-%m-%d %H:%i:%s' ) as end_time, ")
						.append(" DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, t.create_user_id, t.create_user_name, ")
						.append(" DATE_FORMAT( t.update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, t.update_user_id, t.update_user_name, ")
						.append(" t.status, t.del_flag, i.instance_id, i.STATUS as wf_status, i.cur_node, jt.dbid_, ")
						.append(" DATE_FORMAT(jt.CREATE_,'%Y-%m-%d %H:%i:%s') as create_ ")
						.append(" FROM jbpm4_task jt ")
						.append(" INNER JOIN jbpm4_participation jp ON jt.DBID_ = jp.TASK_  ")
						.append(" INNER JOIN wf_process_instance i ON jt.PROCINST_ = i.INSTANCE_ID AND i.FORM_TYPE_ID=:formTypeId ")
						.append(" INNER JOIN t_task t ON t.id = i.FORM_ID ")
						.append(" WHERE ")
						.append(" jp.TYPE_=:type AND jp.USERID_=:userId ")
						.append(" AND t.del_flag =:delFlag ");
		propertyNames.add("formTypeId");
		propertyValues.add(formTypeId);
		propertyNames.add("type");
		propertyValues.add(ActorType.USER.getIndex());
		propertyNames.add("userId");
		propertyValues.add(filterTask.getCreateUserId());
		propertyNames.add("delFlag");
		propertyValues.add(Constants.NORMAL_FLAG);
		/*if (null != filterTask.getId()) {
			querySqlBuffer.append(" AND t.id =:id ");
			propertyNames.add("id");
			propertyValues.add(filterTask.getId());
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterTask.getName()))) {
			querySqlBuffer.append(" AND t.name LIKE :name ");
			propertyNames.add("name");
			propertyValues.add("%" + filterTask.getName() + "%");
		}
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterTask.getRemark()))) {
			querySqlBuffer.append(" AND t.remark LIKE :remark ");
			propertyNames.add("remark");
			propertyValues.add("%" + filterTask.getRemark() + "%");
		}
		if (null != filterTask.getProjectIndexId()) {
			querySqlBuffer.append(" AND t.project_index_id in (SELECT id FROM t_project_index WHERE FIND_IN_SET(id, queryChildrenProjectIndex(:projectIndexId))) ");
			propertyNames.add("projectIndexId");
			propertyValues.add(filterTask.getProjectIndexId());
		}*/
		
		this.createQuerySql(filterTask, querySqlBuffer, propertyNames, propertyValues);
		
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		
		/*if (null != filterTask.getCreateTime()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) >= DATE_FORMAT(:createTime, '%Y-%m-%d %H:%i:%s') ");
			propertyNames.add("createTime");
			propertyValues.add(filterTask.getCreateTime());
		}
		if (null != filterTask.getId()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) =< DATE_FORMAT(?, '%Y-%m-%d %H:%i:%s') ");
		}*/
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY jt.CREATE_ DESC, t.id DESC");
		if (0 < totalCount) {
			List<Map<String, Object>> list = this.setPageParameter(page, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(list);
		}
		return page;
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public int countTodoTasks(Task filterTask, String formTypeId) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySqlBuffer.append(" SELECT t.id, t.name, t.remark, t.plan_amount, t.fact_amount, t.project_index_id, t.progress, t.task_actor_names, ")
						.append("  DATE_FORMAT( t.start_time, '%Y-%m-%d %H:%i:%s' ) as start_time, ")
						.append("  DATE_FORMAT( t.end_time, '%Y-%m-%d %H:%i:%s' ) as end_time, ")
						.append(" DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, t.create_user_id, t.create_user_name, ")
						.append(" DATE_FORMAT( t.update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, t.update_user_id, t.update_user_name, ")
						.append(" t.status, t.del_flag, i.instance_id, i.STATUS as wf_status, i.cur_node, jt.dbid_, ")
						.append(" DATE_FORMAT(jt.CREATE_,'%Y-%m-%d %H:%i:%s') as create_ ")
						.append(" FROM jbpm4_task jt ")
						.append(" INNER JOIN jbpm4_participation jp ON jt.DBID_ = jp.TASK_  ")
						.append(" INNER JOIN wf_process_instance i ON jt.PROCINST_ = i.INSTANCE_ID AND i.FORM_TYPE_ID=:formTypeId ")
						.append(" INNER JOIN t_task t ON t.id = i.FORM_ID ")
						.append(" WHERE ")
						.append(" jp.TYPE_=:type AND jp.USERID_=:userId ")
						.append(" AND t.del_flag =:delFlag ");
		propertyNames.add("formTypeId");
		propertyValues.add(formTypeId);
		propertyNames.add("type");
		propertyValues.add(ActorType.USER.getIndex());
		propertyNames.add("userId");
		propertyValues.add(filterTask.getCreateUserId());
		propertyNames.add("delFlag");
		propertyValues.add(Constants.NORMAL_FLAG);

		this.createQuerySql(filterTask, querySqlBuffer, propertyNames, propertyValues);
		
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		
		/*if (null != filterTask.getCreateTime()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) >= DATE_FORMAT(:createTime, '%Y-%m-%d %H:%i:%s') ");
			propertyNames.add("createTime");
			propertyValues.add(filterTask.getCreateTime());
		}
		if (null != filterTask.getId()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) =< DATE_FORMAT(?, '%Y-%m-%d %H:%i:%s') ");
		}*/
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		return totalCount;
	}

	@Override
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Long> getAllTaskIdsByProjectIndexId(Long projectIndexId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT id FROM t_task WHERE project_index_id in ( ")
				.append(" SELECT p.id FROM t_project_index p WHERE FIND_IN_SET(id, queryChildrenProjectIndex(:projectIndexId)) )");
		List propertyValues = new ArrayList();
		List<String> propertyNames = new ArrayList<String>();
		propertyNames.add("projectIndexId");
		propertyValues.add(projectIndexId);
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames, propertyValues);
//		query.addEntity(Long.class);
		List list = query.list();
		List<Long> rs = new ArrayList<Long>();
		if (CollectionUtils.isNotEmpty(list)) {
			for (Object o : list) {
				rs.add(new Long(o.toString()));
			}
		}
		return rs;
	}

	@Override
	public Integer findTaskCount(Long projectIndex) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT COUNT(1) FROM t_task t ")
					.append(" WHERE t.project_index_id IN ( ")
					.append(" SELECT tpi.id ")
					.append(" FROM t_project_index tpi ")
					.append(" WHERE FIND_IN_SET(tpi.id, queryChildrenProjectIndex(?)) ")
					.append(" ) AND t.del_flag=1 ");
		return this.findSqlInt(querySql.toString(), new Object[]{projectIndex});
	}

	@Override
	public String findTaskAmount(Long projectIndex, String type) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT SUM(IFNULL(t.").append(type).append("_amount, '0.00') + 0.00) FROM t_task t ")
			.append(" WHERE t.project_index_id in ( ")
			.append(" SELECT pi.id FROM t_project_index pi WHERE FIND_IN_SET(id, queryChildrenProjectIndex(?)) ") 
			.append(" ) AND t.del_flag=?; ");
		Object rs = this.findUniqueBySql(querySql.toString(), new Object[]{projectIndex, Constants.NORMAL_FLAG});
		if (null != rs) {
			return rs.toString();
		}
		return "0.00";
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Page<Map<String, Object>> findMyTasks(
			EasyuiPage<Map<String, Object>> page, Task filterTask,
			String formTypeId) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySqlBuffer.append(" SELECT t.id, t.name, t.remark, t.plan_amount, t.fact_amount, ")
						.append("  DATE_FORMAT( t.start_time, '%Y-%m-%d %H:%i:%s' ) as start_time, ")
						.append("  DATE_FORMAT( t.end_time, '%Y-%m-%d %H:%i:%s' ) as end_time, ")
						.append(" t.project_index_id, t.progress, t.task_actor_names,  ")
						.append(" DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, t.create_user_id, t.create_user_name, ")
						.append(" DATE_FORMAT( t.update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, t.update_user_id, t.update_user_name, ")
						.append(" t.status, t.del_flag, i.instance_id, i.STATUS as wf_status, i.cur_node ")
						.append(" FROM t_task t ")
						.append(" LEFT JOIN wf_process_instance i ON t.id = i.FORM_ID ")
						.append(" AND i.FORM_TYPE_ID =:formTypeId ")
						.append(" WHERE ")
						.append(" t.del_flag =:delFlag ")
						.append(" AND ( CONCAT(',',t.task_actor_ids,',') LIKE :taskActorIds ")
						.append(" OR t.create_user_id =:createUserId ")
						.append(" OR (i.INSTANCE_ID IN ( ")
						.append(" SELECT jt.PROCINST_ FROM jbpm4_participation jp, jbpm4_task jt ")
						.append("  WHERE jp.TASK_ = jt.DBID_ AND jp.TYPE_='3' AND jp.USERID_=:pUserId)) ")
						.append(" OR ( i.INSTANCE_ID in ( ")
						.append(" SELECT wt.INSTANCE_ID FROM wf_task wt WHERE wt.TRANSACTOR_ID =:wtUserId)) ")
						.append(" ) ");
						
		propertyNames.add("formTypeId");
		propertyValues.add(formTypeId);
		propertyNames.add("delFlag");
		propertyValues.add(Constants.NORMAL_FLAG);
		propertyNames.add("taskActorIds");
		propertyValues.add("%," + filterTask.getTaskActorIds() + ",%");
		propertyNames.add("createUserId");
		propertyValues.add(filterTask.getCreateUserId());
		propertyNames.add("pUserId");
		propertyValues.add(filterTask.getCreateUserId());
		propertyNames.add("wtUserId");
		propertyValues.add(filterTask.getCreateUserId());
		
		this.createQuerySql(filterTask, querySqlBuffer, propertyNames, propertyValues);
		
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS COUNT ");
		
		/*if (null != filterTask.getCreateTime()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) >= DATE_FORMAT(:createTime, '%Y-%m-%d %H:%i:%s') ");
			propertyNames.add("createTime");
			propertyValues.add(filterTask.getCreateTime());
		}
		if (null != filterTask.getId()) {
			querySqlBuffer.append(" AND DATE_FORMAT( t.create_time, '%Y-%m-%d %H:%i:%s' ) =< DATE_FORMAT(?, '%Y-%m-%d %H:%i:%s') ");
		}*/
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY t.create_time DESC");
		if (0 < totalCount) {
			List<Map<String, Object>> list = this.setPageParameter(page, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(list);
		}
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> findCountTaskProgress(Long projectIndexId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT ")
				.append(" (SELECT count(1) FROM t_task t ") 
				.append(" WHERE t.project_index_id in ( ")
				.append(" SELECT pi.id FROM t_project_index pi WHERE FIND_IN_SET(id,queryChildrenProjectIndex(:projectIndex1)) ")
				.append(" ) ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) >=0 ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) <=25 ")
				.append(" AND t.del_flag = 1) level1, ")
				.append(" (SELECT count(1) FROM t_task t ") 
				.append(" WHERE t.project_index_id in ( ")
				.append(" SELECT pi.id FROM t_project_index pi WHERE FIND_IN_SET(id,queryChildrenProjectIndex(:projectIndex2)) ")
				.append(" ) ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) >25 ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) <=50 ")
				.append(" AND t.del_flag = 1) level2, ")
				.append(" (SELECT count(1) FROM t_task t ") 
				.append(" WHERE t.project_index_id in ( ")
				.append(" SELECT pi.id FROM t_project_index pi WHERE FIND_IN_SET(id,queryChildrenProjectIndex(:projectIndex3)) ")
				.append(" ) ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) >50 ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) <=75 ")
				.append(" AND t.del_flag = 1) level3, ")
				.append(" (SELECT count(1) FROM t_task t ") 
				.append(" WHERE t.project_index_id in ( ")
				.append(" SELECT pi.id FROM t_project_index pi WHERE FIND_IN_SET(id,queryChildrenProjectIndex(:projectIndex4)) ")
				.append(" ) ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) >75 ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) <100 ")
				.append(" AND t.del_flag = 1) level4, ")
				.append(" (SELECT count(1) FROM t_task t ") 
				.append(" WHERE t.project_index_id in ( ")
				.append(" SELECT pi.id FROM t_project_index pi WHERE FIND_IN_SET(id,queryChildrenProjectIndex(:projectIndex5)) ")
				.append(" ) ")
				.append(" AND CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)) = 100.00 ")
				.append(" AND t.del_flag = 1) level5 ");
		List<String> propertyNames = new ArrayList<String>();
		propertyNames.add("projectIndex1");
		propertyNames.add("projectIndex2");
		propertyNames.add("projectIndex3");
		propertyNames.add("projectIndex4");
		propertyNames.add("projectIndex5");
		@SuppressWarnings("rawtypes")
		List propertyValues = new ArrayList();
		propertyValues.add(projectIndexId);
		propertyValues.add(projectIndexId);
		propertyValues.add(projectIndexId);
		propertyValues.add(projectIndexId);
		propertyValues.add(projectIndexId);
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames , propertyValues );
		return (Map<String, Object>) query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
	}

	@Override
	public String findTaskAvgProgress(Long projectIndexId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT SUM(CAST(IFNULL(t.progress,0.00) AS DECIMAL(10,2)))/COUNT(1) FROM t_task t WHERE t.project_index_id = ? AND t.del_flag=? ");
		Object rs = this.findUniqueBySql(querySql.toString(), new Object[]{projectIndexId, Constants.NORMAL_FLAG});
		if (null != rs) {
			return rs.toString();
		}
		return "0.00";
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> findCountTaskInfo(Long projectIndexId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT  COUNT(1) as count_, ")
					.append(" SUM(CAST(IFNULL(t.plan_amount, '0.00') AS DECIMAL(9,2))) as plan_amount_, ")
					.append(" SUM(CAST(IFNULL(t.fact_amount, '0.00') AS DECIMAL(9,2))) as fact_amount_, ")
					.append(" SUM(CAST(IFNULL(t.plan_amount, '0.00') AS DECIMAL(9,2)) - CAST(IFNULL(t.fact_amount, '0.00') AS DECIMAL(9,2))) as unuser_amount_ ")
					.append(" FROM t_task t ")
					.append(" WHERE t.project_index_id IN ( ")
					.append(" SELECT tpi.id FROM t_project_index tpi ")
					.append(" WHERE FIND_IN_SET(tpi.id, queryChildrenProjectIndex (:projectIndexId)) ")
					.append(" ) AND t.del_flag = 1 ");
		List<String> propertyNames = new ArrayList<String>();
		propertyNames.add("projectIndexId");
		@SuppressWarnings("rawtypes")
		List propertyValues = new ArrayList();
		propertyValues.add(projectIndexId);
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames , propertyValues );
		return (Map<String, Object>) query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
	}
}
